#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Author:Summerday


import sqlite3
import csv
cx = sqlite3.connect("D:\\comp\\tdzl\\CBDB_20200528.db")
sql = """
select b.c_personid personId , b.c_female female,b.c_name_chn name,
       b.c_index_year indexYear,
       a.c_name_chn addrName,a.x_coord x,a.y_coord y
from BIOG_MAIN b
left join BIOG_ADDR_DATA bad on bad.c_personid = b.c_personid
left join ADDRESSES a on a.c_addr_id = bad.c_addr_id
left join BIOG_ADDR_CODES bac on bac.c_addr_type = bad.c_addr_type

where b.c_personid
in (select distinct b.c_personid  from BIOG_MAIN b
left  join ASSOC_DATA AD on b.c_personid = AD.c_personid
left join ASSOC_CODES ac on ac.c_assoc_code = AD.c_assoc_code
left join ASSOC_CODE_TYPE_REL ACTR on ac.c_assoc_code = ACTR.c_assoc_code
left join ASSOC_TYPES AT on AT.c_assoc_type_id = ACTR.c_assoc_type_id
 where b.c_birthyear
     between 1368 and 1644
   and b.c_death_age!=0
   and AT.c_assoc_type_parent_id = '02') or b.c_personid in
(select distinct AD.c_assoc_id  from BIOG_MAIN b
 left  join ASSOC_DATA AD on b.c_personid = AD.c_personid
 left join ASSOC_CODES ac on ac.c_assoc_code = AD.c_assoc_code
 left join ASSOC_CODE_TYPE_REL ACTR on ac.c_assoc_code = ACTR.c_assoc_code
 left join ASSOC_TYPES AT on AT.c_assoc_type_id = ACTR.c_assoc_type_id
 where b.c_birthyear
     between 1368 and 1644
   and b.c_death_age!=0
   and AT.c_assoc_type_parent_id = '02')
   and b.c_index_year is not null
   and a.x_coord is not null
and a.y_coord is not null
group by personId
having x is not null;

"""

data = cx.execute(sql)
data_list = data.fetchall()

print("开始导入数据！")
with open('csv/nodes.csv','w',encoding='utf-8',newline='') as f:
	w = csv.writer(f)
	w.writerow(['personId','female','name','indexYear','addrName','x','y'])
	for d in data_list:
		w.writerow(list(d))
		
print("导入数据成功！")